package ua.kharkov.knure.dereza.summarytask4.db.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import ua.kharkov.knure.dereza.summarytask4.db.dao.ClientDAO;
import ua.kharkov.knure.dereza.summarytask4.db.dto.ClientDTO;

/**
 * Data access object for Client
 * 
 * @author dereza
 * 
 */
public class MysqlClientDAO implements ClientDAO {

	private static final Logger log = Logger.getLogger(MysqlClientDAO.class);

	private Connection conn;

	// ---------------------------------------------
	// SQL queries
	// ---------------------------------------------
	private static final String SQL_FIND_CLIENT_BY_LOGIN = "SELECT * FROM clients WHERE login=?";
	private static final String SQL_FIND_CLIENT_BY_ID = "SELECT * FROM clients WHERE id=?";
	private static final String SQL_FIND_CLIENT_BY_CARD_ID = "SELECT * FROM clients WHERE id IN (SELECT id_client FROM clients_cards WHERE id_card=?)";
	private static final String SQL_UPDATE_CLIENT_INFO = "UPDATE clients SET name=?, surname=?, mobile_number=?, locale_id=? WHERE id=?";
	
	@Override
	public ClientDTO findClientById(int id) {
		ClientDTO client = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = MysqlDAOFactory.createConnection();
			pstmt = conn.prepareStatement(SQL_FIND_CLIENT_BY_ID);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				client = extractClientDTO(rs);
			}
		} catch (SQLException ex) {
			log.error("Cannot do query", ex);
			rollback(conn);
		} finally {
			commitAndClose(rs, pstmt, conn);
		}
		return client;
	}

	@Override
	public ClientDTO findClientByLogin(String login) {
		ClientDTO client = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = MysqlDAOFactory.createConnection();
			pstmt = conn.prepareStatement(SQL_FIND_CLIENT_BY_LOGIN);
			pstmt.setString(1, login);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				client = extractClientDTO(rs);
			}
		} catch (SQLException ex) {
			log.error("Cannot do query", ex);
			rollback(conn);
		} finally {
			commitAndClose(rs, pstmt, conn);
		}
		return client;
	}

	@Override
	public ClientDTO findClientByCardId(int cardId) {
		ClientDTO client = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = MysqlDAOFactory.createConnection();
			pstmt = conn.prepareStatement(SQL_FIND_CLIENT_BY_CARD_ID);
			pstmt.setInt(1, cardId);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				client = extractClientDTO(rs);
			}
		} catch (SQLException ex) {
			log.error("Cannot do query", ex);
			rollback(conn);
		} finally {
			commitAndClose(rs, pstmt, conn);
		}
		return client;
	}
	
	@Override
	public void updateClient(ClientDTO clientDTO) {
		PreparedStatement pstmt = null;
		String name = clientDTO.getName();
		String surName = clientDTO.getSurName();
		String mobileNumber = clientDTO.getMobileNumber();
		int locale = clientDTO.getLocale();
		int id = clientDTO.getId();
		try{
			conn = MysqlDAOFactory.createConnection();
			pstmt = conn.prepareStatement(SQL_UPDATE_CLIENT_INFO);
			pstmt.setString(1, name);
			pstmt.setString(2, surName);
			pstmt.setString(3, mobileNumber);
			pstmt.setInt(4, locale);
			pstmt.setInt(5, id);
			pstmt.executeUpdate();
		} catch (SQLException ex) {
			log.error("Cannot do query", ex);
			rollback(conn);
			throw new Error(ex);
		} finally {
			commitAndClose(null, pstmt, conn);
		}
	}
	
	@Override
	public ClientDTO extractClientDTO(ResultSet rs) throws SQLException {
		ClientDTO client = new ClientDTO();
		client.setId(rs.getInt(1));
		client.setLogin(rs.getString(2));
		client.setPassword(rs.getString(3));
		client.setSurName(rs.getString(4));
		client.setName(rs.getString(5));
		client.setMobileNumber(rs.getString(6));
		client.setRole(rs.getInt(7));
		client.setLocale(rs.getInt(8));
		return client;
	}

	@Override
	public void commitAndClose(ResultSet rs, Statement ps, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				log.error("Cannot close result set", e);
			}
		}
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				log.error("Cannot close statement", e);
			}
		}
		if (conn != null) {
			try {
				conn.commit();
				conn.close();
			} catch (SQLException e) {
				log.error("Cannot commit and close connection", e);
			}
		}
	}

	@Override
	public void rollback(Connection conn) {
		if (conn != null) {
			try {
				conn.rollback();
			} catch (SQLException e) {
				log.error("Cannot rollback", e);
			}
		}
	}
}